<?php
class QArchive
{	
	public function __construct($p_strCountry, $p_stTableName=null)
	{
		$this->_country		= $p_strCountry;
		$this->_table		= $p_stTableName ? $p_stTableName : strtolower($p_strCountry).'_archives';
	}
	
	public function get_all()
	{
		$strQuery	= '
			SELECT sku
			FROM '.$this->_table.'
			';
		$db 	= Zend_Registry::get('db');
		$resultValue =$db->fetchAll($strQuery);
		$db->closeConnection();
		return $resultValue;
	}
	
	private function _whereSearch($p_arrCategory, $p_arrParams)
	{
		$strQuery		= null;
		if($p_arrCategory){
			$strQuery	= $strQuery.' AND LOWER(t1.category) IN ("'.strtolower(implode('","', $p_arrCategory)).'")';
		}
		if(isset($p_arrParams['sku']) && $p_arrParams['sku'] ){
			$strQuery	= $strQuery.' AND ( LOWER(t1.sku) like "%'.strtolower($p_arrParams['sku']).'%"'
				.' OR LOWER(t1.product_name) like "%'.strtolower($p_arrParams['sku']).'%")';
		};
		
		if(isset($p_arrParams['supplier']) && $p_arrParams['supplier'] ){
			$strQuery	= $strQuery.' AND t1.supplier_id ="'.strtolower($p_arrParams['supplier']).'"';
		};
		
		if(isset($p_arrParams['total']) && $p_arrParams['total'] )
		{
			if($p_arrParams['total'] == '11')
			{
				$strQuery	= $strQuery.' AND t1.total_sales > 0 ' ;
			}
			else
			{
				$strQuery	= $strQuery.' AND t1.total_sales > ' . $p_arrParams['total'] . ' ' ;
			}
		};
		
		if(isset($p_arrParams['asl']) && $p_arrParams['asl'] )
		{
			if($p_arrParams['asl'] =='11')
			{
				$strQuery	= $strQuery.' AND t1.asl > 0 ' ;
			}
			else
			{
				$strQuery	= $strQuery.' AND t1.asl > ' . $p_arrParams['asl'] . ' ' ;
			}
		};
		
		if(isset($p_arrParams['reorder']) && $p_arrParams['reorder'] ){
			$strQuery	= $strQuery.' AND LOWER(t1.reorder)="'.strtolower($p_arrParams['reorder']).'"';
		};
		return $strQuery;
	}
	
	public function get($p_intOffset, $p_intLimit, $p_arrCategory=null, $p_arrParams=null, $export)
	{
		if($export)
		{
			$strQuery	= 'SELECT t1.*, m.brand as brand  
		    					FROM '.$this->_table.' t1 Left join ' . strtolower($this->_country). '_product_images m ON m.sku  = SUBSTRING_INDEX(t1.sku, "-", 1) ';	
		}
		else
		{
			$strQuery	= '
			SELECT t1.*, m.link_image as linkimage, m.name as name, m.description as description, m.brand as brand  
			FROM '.$this->_table.' t1 Left join ' . strtolower($this->_country). '_product_images m ON m.sku  = SUBSTRING_INDEX(t1.sku, "-", 1) ';
		}
		$strQuery	= $strQuery. ' WHERE 1=1';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrCategory, $p_arrParams);
		
		if( isset($p_arrParams['orf']) &&  $p_arrParams['orf'])
		{
			if($p_arrParams['orv'] =='default')
			{
				$strQuery	= $strQuery .' ORDER BY ' . ' bucket_id ASC, reorder DESC, total_sales DESC ';
			}
			else
			{
				$strQuery	= $strQuery .' ORDER BY '.$p_arrParams['orf'].' '.$p_arrParams['orv'];
			}
		}
		
		if($p_intLimit!='all'){
			$strQuery	= $strQuery.' LIMIT '.$p_intOffset.','.$p_intLimit;
		}
		
		$db 	= Zend_Registry::get('db');
		$resultValue = $db->fetchAll($strQuery);
		$db->closeConnection();
		
		return $resultValue;
	}
	
	public function total($p_arrCategory=null, $p_arrParams=null)
	{
		$strQuery	= '
			SELECT COUNT(t1.id) `total`
			FROM '.$this->_table.' t1
			';
		$strQuery	= $strQuery. ' WHERE 1=1';
		$strQuery	= $strQuery. $this->_whereSearch($p_arrCategory, $p_arrParams);
		
		$db 		= Zend_Registry::get('db');
		$arrResult 	= $db->fetchAll($strQuery);
		$db->closeConnection();
		
		return isset($arrResult[0]['total']) ? $arrResult[0]['total'] : 0;
	}

	public function get_by_id($p_intId)
	{
		$strQuery	= 'SELECT * FROM '.$this->_table.' WHERE id='.intval($p_intId);
		
		$db 		= Zend_Registry::get('db');
		$resultValue =$db->fetchAll($strQuery);
		$db->closeConnection();
		
		return $resultValue; 
	}
	
	public function insert($p_arrParams)
	{
		$db	= Zend_Registry::get('db');
		$resultValue =$db->insert($this->_table, $p_arrParams);
		$db->closeConnection();
		return $resultValue;
	}
	
	public function update($p_arrParams, $p_intId)
	{
		$db	= Zend_Registry::get('db');
		$resultValue = $db->update($this->_table, $p_arrParams, 'id='.intval($p_intId));
		$db->closeConnection();
		
		return $resultValue;
	}
	
	public function reset()
	{
		$strQuery	= 'TRUNCATE '.$this->_table;
		$db 		= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function add_column($p_strColumn, $p_strDataType)
	{
		$strQuery	= 'ALTER TABLE '.$this->_table.' ADD COLUMN '.strtolower($p_strColumn).' '.strtolower($p_strDataType).' AFTER id';
		$db			= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
	
	public function clone_table($p_intAdminId)
	{
		$strTable	= $this->_country.'_archives_'.date('YmdHis');
		$strQuery	= '
			CREATE TABLE '.$strTable.' LIKE '.$this->_table.';
			INSERT '.$strTable.' SELECT * FROM '.$this->_table.';
			INSERT INTO '.$this->_country.'_archive_logs(iname, createdate, admin_id) VALUES("'.$strTable.'", sysdate(), "'.$p_intAdminId.'");';
		$db			= Zend_Registry::get('db');
		$db->execute($strQuery);
		$db->closeConnection();
	}
}
?>